﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class OrderDetailDA : IOrderDetailDA
    {
        private string ACTUAL_SHIP_DATE = "@ACTUAL_SHIP_DATE";
        private string ADJUSTED_QTY = "@ADJUSTED_QTY";
        private string ALLOCATE_RULE_ID = "@ALLOCATE_RULE_ID";
        private string ALLOCATE_STRATEGY_TYPE = "@ALLOCATE_STRATEGY_TYPE";
        private string ALLOCATED_QTY = "@ALLOCATED_QTY";
        private string ALT_SKU_ID = "@ALT_SKU_ID";
        private string BUYER_PO = "@BUYER_PO";
        private string CARTON_BREAK = "@CARTON_BREAK";
        private string CARTON_GROUP = "@CARTON_GROUP";
        private string CARTON_QTY_BREAK = "@CARTON_QTY_BREAK";
        private string COMPONENT_QTY = "@COMPONENT_QTY";
        private string COMPONENT_REFERENCE = "@COMPONENT_REFERENCE";
        private string CREATED_BY = "@CREATED_BY";
        private string EFFECTIVE_DATE = "@EFFECTIVE_DATE";
        private string EXPIRY_DATE = "@EXPIRY_DATE";
        private string EXTENDED_PRICE = "@EXTENDED_PRICE";
        private string EXTERN_LINE_ID = "@EXTERN_LINE_ID";
        private string EXTERN_ORDER_ID = "@EXTERN_ORDER_ID";
        private string EXTERNAL_LOC_SEQUENCE = "@EXTERNAL_LOC_SEQUENCE";
        private string EXTERNAL_LOT = "@EXTERNAL_LOT";
        private string IS_SUBSTITUTE = "@IS_SUBSTITUTE";
        private string LINE_ID = "@LINE_ID";
        private string LINE_TYPE = "@LINE_TYPE";
        private string LOT = "@LOT";
        private string LOT_ATTR01 = "@LOT_ATTR01";
        private string LOT_ATTR02 = "@LOT_ATTR02";
        private string LOT_ATTR03 = "@LOT_ATTR03";
        private string LOT_ATTR04 = "@LOT_ATTR04";
        private string LOT_ATTR05 = "@LOT_ATTR05";
        private string LOT_ATTR06 = "@LOT_ATTR06";
        private string LOT_ATTR07 = "@LOT_ATTR07";
        private string LOT_ATTR08 = "@LOT_ATTR08";
        private string LOT_ATTR09 = "@LOT_ATTR09";
        private string LOT_ATTR10 = "@LOT_ATTR10";
        private string LPN_ID = "@LPN_ID";
        private string MIN_SHIPPER_CENT = "@MIN_SHIPPER_CENT";
        private string OK_TO_SUBSTITUTE = "@OK_TO_SUBSTITUTE";
        private string OPEN_QTY = "@OPEN_QTY";
        private string OPP_REQUEST = "@OPP_REQUEST";
        private string ORDER_ID = "@ORDER_ID";
        private string ORIG_CASE_QTY = "@ORIG_CASE_QTY";
        private string ORIG_PALLET_QTY = "@ORIG_PALLET_QTY";
        private string ORIGINAL_LINE_ID = "@ORIGINAL_LINE_ID";
        private string ORIGINAL_QTY = "@ORIGINAL_QTY";
        private string ORIGINAL_SKU = "@ORIGINAL_SKU";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PALLET_ID = "@PALLET_ID";
        private string PICK_CODE = "@PICK_CODE";
        private string PICKED_QTY = "@PICKED_QTY";
        private string PICKING_INSTRUCTIONS = "@PICKING_INSTRUCTIONS";
        private string PREALLOCATE_RULE_ID = "@PREALLOCATE_RULE_ID";
        private string PREALLOCATED_QTY = "@PREALLOCATED_QTY";
        private string PRODUCE_DATE = "@PRODUCE_DATE";
        private string PRODUCT_CUBE = "@PRODUCT_CUBE";
        private string PRODUCT_WEIGHT = "@PRODUCT_WEIGHT";
        private string QC_AUTO_ADJUST = "@QC_AUTO_ADJUST";
        private string QC_REQUIRED = "@QC_REQUIRED";
        private string QTY_INTRANSIT = "@QTY_INTRANSIT";
        private string RECEIPT_DATE = "@RECEIPT_DATE";
        private string REMARK = "@REMARK";
        private string ROTATION = "@ROTATION";
        private string SHELF_LIFE = "@SHELF_LIFE";
        private string SHIP_GROUP01 = "@SHIP_GROUP01";
        private string SHIP_GROUP02 = "@SHIP_GROUP02";
        private string SHIP_GROUP03 = "@SHIP_GROUP03";
        private string SHIPPED_QTY = "@SHIPPED_QTY";
        private string SKU_ID = "@SKU_ID";
        private string SKU_ROTATION = "@SKU_ROTATION";
        private string SQL_CHECK_ORDER_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_ORDER_DETAIL = " DELETE FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_ORDER_DETAIL_BY_ORDER_ID = " DELETE FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_INSERT_ORDER_DETAIL = " INSERT INTO @_@ORDER_DETAIL ( WH_ID, ORDER_ID, LINE_ID, EXTERN_ORDER_ID, EXTERN_LINE_ID, SKU_ID, ALT_SKU_ID, OWNER_ID, ORIGINAL_QTY, OPEN_QTY, SHIPPED_QTY, ADJUSTED_QTY, PREALLOCATED_QTY, ALLOCATED_QTY, PICKED_QTY, UOM_ID, PACK_ID, PICK_CODE, CARTON_GROUP, LPN_ID, STATUS, UNIT_PRICE, TAX01, TAX02, EXTENDED_PRICE, UPDATE_SOURCE, PRODUCT_WEIGHT, PRODUCT_CUBE, CARTON_BREAK, CARTON_QTY_BREAK, QC_REQUIRED, QC_AUTO_ADJUST, SHIP_GROUP01, SHIP_GROUP02, SHIP_GROUP03, TARIFF_ID, ORIGINAL_LINE_ID, ORIGINAL_SKU, BUYER_PO, ACTUAL_SHIP_DATE, IS_SUBSTITUTE, OK_TO_SUBSTITUTE, ALLOCATE_RULE_ID, PREALLOCATE_RULE_ID, ALLOCATE_STRATEGY_TYPE, SKU_ROTATION, SHELF_LIFE, ROTATION, SUB_FLAG, WORK_ORDER_ID, LINE_TYPE, COMPONENT_QTY, COMPONENT_REFERENCE, LOT, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, EXTERNAL_LOT,EFFECTIVE_DATE, PALLET_ID, ORIG_CASE_QTY, ORIG_PALLET_QTY, OPP_REQUEST, PICKING_INSTRUCTIONS, QTY_INTRANSIT, WP_RELEASED, EXTERNAL_LOC_SEQUENCE, MIN_SHIPPER_CENT, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, VENDOR_ID,RECEIPT_DATE) VALUES ( @WH_ID, @ORDER_ID, @LINE_ID, @EXTERN_ORDER_ID, @EXTERN_LINE_ID, @SKU_ID, @ALT_SKU_ID, @OWNER_ID, @ORIGINAL_QTY, @OPEN_QTY, @SHIPPED_QTY, @ADJUSTED_QTY, @PREALLOCATED_QTY, @ALLOCATED_QTY, @PICKED_QTY, @UOM_ID, @PACK_ID, @PICK_CODE, @CARTON_GROUP, @LPN_ID, @STATUS, @UNIT_PRICE, @TAX01, @TAX02, @EXTENDED_PRICE, @UPDATE_SOURCE, @PRODUCT_WEIGHT, @PRODUCT_CUBE, @CARTON_BREAK, @CARTON_QTY_BREAK, @QC_REQUIRED, @QC_AUTO_ADJUST, @SHIP_GROUP01, @SHIP_GROUP02, @SHIP_GROUP03, @TARIFF_ID, @ORIGINAL_LINE_ID, @ORIGINAL_SKU, @BUYER_PO, @ACTUAL_SHIP_DATE, @IS_SUBSTITUTE, @OK_TO_SUBSTITUTE, @ALLOCATE_RULE_ID, @PREALLOCATE_RULE_ID, @ALLOCATE_STRATEGY_TYPE, @SKU_ROTATION, @SHELF_LIFE, @ROTATION, @SUB_FLAG, @WORK_ORDER_ID, @LINE_TYPE, @COMPONENT_QTY, @COMPONENT_REFERENCE, @LOT, @LOT_ATTR01, @LOT_ATTR02, @LOT_ATTR03, @LOT_ATTR04, @LOT_ATTR05, @LOT_ATTR06, @LOT_ATTR07, @LOT_ATTR08, @LOT_ATTR09, @LOT_ATTR10, @PRODUCE_DATE, @EXPIRY_DATE, @EXTERNAL_LOT, @EFFECTIVE_DATE, @PALLET_ID, @ORIG_CASE_QTY, @ORIG_PALLET_QTY, @OPP_REQUEST, @PICKING_INSTRUCTIONS, @QTY_INTRANSIT, @WP_RELEASED, @EXTERNAL_LOC_SEQUENCE, @MIN_SHIPPER_CENT, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@VENDOR_ID,@RECEIPT_DATE )  ";
        private string SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT = " SELECT O.INTRANSIT_ID, OD.ROWID, OD.WH_ID, OD.ORDER_ID,  OD.LINE_ID, OD.EXTERN_ORDER_ID, OD.EXTERN_LINE_ID, OD.SKU_ID, OD.ALT_SKU_ID, OD.OWNER_ID,SKU.DESCR AS SKU_DESCR, OD.ORIGINAL_QTY, OD.OPEN_QTY, OD.SHIPPED_QTY, OD.ADJUSTED_QTY, OD.PREALLOCATED_QTY,   OD.ALLOCATED_QTY, OD.PICKED_QTY, OD.UOM_ID, OD.PACK_ID, OD.PICK_CODE, OD.CARTON_GROUP, OD.LPN_ID,T.[TEXT] AS STATUS_D, OD.STATUS, OD.UNIT_PRICE, OD.TAX01, OD.TAX02, OD.EXTENDED_PRICE, OD.UPDATE_SOURCE, OD.PRODUCT_WEIGHT, OD.PRODUCT_CUBE,   OD.CARTON_BREAK, OD.CARTON_QTY_BREAK, OD.QC_REQUIRED, OD.QC_AUTO_ADJUST, OD.SHIP_GROUP01, OD.SHIP_GROUP02, OD.SHIP_GROUP03, OD.TARIFF_ID, OD.ORIGINAL_LINE_ID, OD.ORIGINAL_SKU, OD.BUYER_PO, OD.IS_SUBSTITUTE, OD.ACTUAL_SHIP_DATE,  OD.OK_TO_SUBSTITUTE, OD.ALLOCATE_RULE_ID, OD.PREALLOCATE_RULE_ID, OD.ALLOCATE_STRATEGY_TYPE, OD.SKU_ROTATION, OD.SHELF_LIFE, OD.ROTATION, OD.SUB_FLAG, OD.WORK_ORDER_ID, OD.LINE_TYPE, OD.COMPONENT_QTY, OD.COMPONENT_REFERENCE, OD.LOT,  OD.LOT_ATTR01, OD.LOT_ATTR02, OD.LOT_ATTR03, OD.LOT_ATTR04, OD.LOT_ATTR05, OD.LOT_ATTR06, OD.LOT_ATTR07, OD.LOT_ATTR08,  OD.LOT_ATTR09, OD.LOT_ATTR10, OD.PRODUCE_DATE, OD.EXPIRY_DATE, OD.RECEIPT_DATE, OD.EXTERNAL_LOT, OD.EFFECTIVE_DATE, OD.PALLET_ID,     OD.ORIG_CASE_QTY, OD.ORIG_PALLET_QTY, OD.OPP_REQUEST,  OD.PICKING_INSTRUCTIONS, OD.QTY_INTRANSIT, OD.WP_RELEASED,  OD.EXTERNAL_LOC_SEQUENCE, OD.MIN_SHIPPER_CENT, OD.UDF1, OD.UDF2,  OD.UDF3, OD.UDF4, OD.UDF5, OD.UDF6, OD.UDF8,   OD.UDF7, OD.UDF9, OD.UDF10, OD.REMARK, OD.TS,  OD.CREATED_BY, OD.CREATED_DATE, OD.UPDATED_BY,  OD.UPDATED_DATE,OD.VENDOR_ID  FROM @_@ORDER_DETAIL OD INNER JOIN @_@ORDERS O ON OD.ORDER_ID = O.ORDER_ID LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = OD.OWNER_ID AND SKU.SKU_ID = OD.SKU_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID";
        private string SQL_SELECT_ALL_ORDER_DETAILA = " SELECT OD.ROWID, OD.WH_ID, OD.ORDER_ID,  OD.LINE_ID, OD.EXTERN_ORDER_ID, OD.EXTERN_LINE_ID, OD.SKU_ID, OD.ALT_SKU_ID, OD.OWNER_ID,SKU.DESCR AS SKU_DESCR, OD.ORIGINAL_QTY, OD.OPEN_QTY, OD.SHIPPED_QTY, OD.ADJUSTED_QTY, OD.PREALLOCATED_QTY,   OD.ALLOCATED_QTY, OD.PICKED_QTY, OD.UOM_ID, OD.PACK_ID, OD.PICK_CODE, OD.CARTON_GROUP, OD.LPN_ID,T.[TEXT] AS STATUS_D, OD.STATUS, OD.UNIT_PRICE, OD.TAX01, OD.TAX02, OD.EXTENDED_PRICE, OD.UPDATE_SOURCE, OD.PRODUCT_WEIGHT, OD.PRODUCT_CUBE,   OD.CARTON_BREAK, OD.CARTON_QTY_BREAK, OD.QC_REQUIRED, OD.QC_AUTO_ADJUST, OD.SHIP_GROUP01, OD.SHIP_GROUP02, OD.SHIP_GROUP03, OD.TARIFF_ID, OD.ORIGINAL_LINE_ID, OD.ORIGINAL_SKU, OD.BUYER_PO, OD.IS_SUBSTITUTE, OD.ACTUAL_SHIP_DATE,  OD.OK_TO_SUBSTITUTE, OD.ALLOCATE_RULE_ID, OD.PREALLOCATE_RULE_ID, OD.ALLOCATE_STRATEGY_TYPE, OD.SKU_ROTATION, OD.SHELF_LIFE, OD.ROTATION, OD.SUB_FLAG, OD.WORK_ORDER_ID, OD.LINE_TYPE, OD.COMPONENT_QTY, OD.COMPONENT_REFERENCE, OD.LOT,  OD.LOT_ATTR01, OD.LOT_ATTR02, OD.LOT_ATTR03, OD.LOT_ATTR04, OD.LOT_ATTR05, OD.LOT_ATTR06, OD.LOT_ATTR07, OD.LOT_ATTR08,  OD.LOT_ATTR09, OD.LOT_ATTR10, OD.PRODUCE_DATE, OD.EXPIRY_DATE, OD.RECEIPT_DATE, OD.EXTERNAL_LOT, OD.EFFECTIVE_DATE, OD.PALLET_ID,     OD.ORIG_CASE_QTY, OD.ORIG_PALLET_QTY, OD.OPP_REQUEST,  OD.PICKING_INSTRUCTIONS, OD.QTY_INTRANSIT, OD.WP_RELEASED,  OD.EXTERNAL_LOC_SEQUENCE, OD.MIN_SHIPPER_CENT, OD.UDF1, OD.UDF2,  OD.UDF3, OD.UDF4, OD.UDF5, OD.UDF6, OD.UDF8,   OD.UDF7, OD.UDF9, OD.UDF10, OD.REMARK, OD.TS,  OD.CREATED_BY, OD.CREATED_DATE, OD.UPDATED_BY,  OD.UPDATED_DATE,OD.VENDOR_ID  FROM @_@ORDER_DETAIL OD  LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = OD.OWNER_ID AND SKU.SKU_ID = OD.SKU_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_ORDER_DETAILA_FOR_CONTAINER = " SELECT  OD.ROWID, OD.WH_ID, OD.ORDER_ID, OD.LINE_ID, OD.EXTERN_ORDER_ID, OD.EXTERN_LINE_ID, OD.SKU_ID, OD.ALT_SKU_ID, OD.OWNER_ID, SKU.DESCR AS SKU_DESCR,OD.ORIGINAL_QTY, OD.OPEN_QTY, OD.SHIPPED_QTY, OD.ADJUSTED_QTY, OD.PREALLOCATED_QTY, OD.ALLOCATED_QTY, OD.PICKED_QTY, OD.UOM_ID, OD.PACK_ID, OD.PICK_CODE, OD.CARTON_GROUP, OD.LPN_ID, OD.STATUS, OD.UNIT_PRICE, OD.TAX01, OD.TAX02, OD.EXTENDED_PRICE, OD.UPDATE_SOURCE, OD.PRODUCT_WEIGHT, OD.PRODUCT_CUBE, OD.CARTON_BREAK, OD.CARTON_QTY_BREAK, OD.QC_REQUIRED, OD.QC_AUTO_ADJUST, OD.SHIP_GROUP01, OD.SHIP_GROUP02, OD.SHIP_GROUP03, OD.TARIFF_ID, OD.ORIGINAL_LINE_ID, OD.ORIGINAL_SKU, OD.BUYER_PO, OD.ACTUAL_SHIP_DATE, OD.IS_SUBSTITUTE, OD.OK_TO_SUBSTITUTE, OD.ALLOCATE_RULE_ID, OD.PREALLOCATE_RULE_ID, OD.ALLOCATE_STRATEGY_TYPE, OD.SKU_ROTATION, OD.SHELF_LIFE, OD.ROTATION, OD.SUB_FLAG, OD.WORK_ORDER_ID, OD.LINE_TYPE, OD.COMPONENT_QTY, OD.COMPONENT_REFERENCE, OD.LOT, OD.LOT_ATTR01, OD.LOT_ATTR02, OD.LOT_ATTR03, OD.LOT_ATTR04, OD.LOT_ATTR05, OD.LOT_ATTR06, OD.LOT_ATTR07, OD.LOT_ATTR08, OD.LOT_ATTR09, OD.LOT_ATTR10, OD.PRODUCE_DATE, OD.EXPIRY_DATE,OD.RECEIPT_DATE, OD.EXTERNAL_LOT, OD.EFFECTIVE_DATE, OD.PALLET_ID, OD.ORIG_CASE_QTY, OD.ORIG_PALLET_QTY, OD.OPP_REQUEST, OD.PICKING_INSTRUCTIONS, OD.QTY_INTRANSIT, OD.WP_RELEASED, OD.EXTERNAL_LOC_SEQUENCE, OD.MIN_SHIPPER_CENT, OD.UDF1, OD.UDF2, OD.UDF3, OD.UDF4, OD.UDF5, OD.UDF6, OD.UDF7, OD.UDF8, OD.UDF9, OD.UDF10, OD.REMARK, OD.TS, OD.CREATED_BY, OD.CREATED_DATE, OD.UPDATED_BY, OD.UPDATED_DATE, (SELECT ISNULL(SUM(SC.QTY),0) FROM  @_@SHIPMENT_CONTAINER SC WHERE SC.ORDER_ID =@ORDER_ID  AND  SC.ORDER_LINE_ID = LINE_ID AND  CONTAINER_ID=@CONTAINER_ID )  AS ENCASED_QTY , (SELECT PICKED_QTY-ISNULL(SUM(SC.QTY),0) FROM  @_@SHIPMENT_CONTAINER SC WHERE SC.ORDER_ID =@ORDER_ID  AND  SC.ORDER_LINE_ID = LINE_ID )  AS UNENCASED_QTY ,VENDOR_ID ,T.[TEXT] AS STATUS_D  FROM  @_@ORDER_DETAIL OD LEFT JOIN @_@SKU SKU ON SKU.OWNER_ID = OD.OWNER_ID AND SKU.SKU_ID = OD.SKU_ID   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = OD.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus'  LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+OD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE  INNER JOIN SYSADM.USER_OWNER UO ON OD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_GET_ORDER_LINE_ID_FOR_SHIPPING = " SELECT DISTINCT LINE_ID FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND (STATUS = 'PARTPICKED' OR STATUS = 'PICKED') ";
        private string SQL_SELECT_INV_QTY_BY_ALLOCATE_STRATEGY = " SELECT INV_LOT_LOC_LPN.LOC, INV_LOT_LOC_LPN.LPN_ID, INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY - INV_LOT_LOC_LPN.EXPECTED_QTY - INV_LOT_LOC_LPN.PICKINPROCESS_QTY - INV_LOT_LOC_LPN.PENDING_MOVE_IN  AS QTYAVAILABLE  FROM   @_@INV_LOT_LOC_LPN INV_LOT_LOC_LPN  LEFT JOIN @_@LOCATION LOCATION ON INV_LOT_LOC_LPN.LOC = LOCATION.LOC LEFT JOIN @_@LPN LPN ON INV_LOT_LOC_LPN.LPN_ID = LPN.LPN_ID WHERE   INV_LOT_LOC_LPN.LOT = @LOT   AND LOCATION.LOC_FLAG <>'HOLD' AND  LOCATION.LOC_FLAG <> 'DAMAGE' AND  (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY - INV_LOT_LOC_LPN.EXPECTED_QTY - INV_LOT_LOC_LPN.PICKINPROCESS_QTY - INV_LOT_LOC_LPN.PENDING_MOVE_IN) > '0'  AND  LOCATION.STATUS <> 'HOLD' AND  (LPN.STATUS <> 'HOLD' OR LPN.STATUS IS NULL OR LPN.STATUS = '')  ";
        private string SQL_UPDATE_ACTUAL_SHIP_DATE_BY_ORDER_ID_LINE_ID = " UPDATE @_@ORDER_DETAIL  SET ACTUAL_SHIP_DATE=@ACTUAL_SHIP_DATE , UPDATED_DATE=@UPDATED_DATE,UPDATED_BY=@UPDATED_BY WHERE ORDER_ID=@ORDER_ID AND LINE_ID=@LINE_ID";
        private string SQL_UPDATE_ORDER_DETAIL = " UPDATE @_@ORDER_DETAIL SET WH_ID = @WH_ID, EXTERN_ORDER_ID = @EXTERN_ORDER_ID, EXTERN_LINE_ID = @EXTERN_LINE_ID, SKU_ID = @SKU_ID, ALT_SKU_ID = @ALT_SKU_ID, OWNER_ID = @OWNER_ID, ORIGINAL_QTY = @ORIGINAL_QTY, OPEN_QTY = @OPEN_QTY, SHIPPED_QTY = @SHIPPED_QTY, ADJUSTED_QTY = @ADJUSTED_QTY, PREALLOCATED_QTY = @PREALLOCATED_QTY, ALLOCATED_QTY = @ALLOCATED_QTY, PICKED_QTY = @PICKED_QTY, UOM_ID = @UOM_ID, PACK_ID = @PACK_ID, PICK_CODE = @PICK_CODE, CARTON_GROUP = @CARTON_GROUP, LPN_ID = @LPN_ID, STATUS = @STATUS, UNIT_PRICE = @UNIT_PRICE, TAX01 = @TAX01, TAX02 = @TAX02, EXTENDED_PRICE = @EXTENDED_PRICE, UPDATE_SOURCE = @UPDATE_SOURCE, PRODUCT_WEIGHT = @PRODUCT_WEIGHT, PRODUCT_CUBE = @PRODUCT_CUBE, CARTON_BREAK = @CARTON_BREAK, CARTON_QTY_BREAK = @CARTON_QTY_BREAK, QC_REQUIRED = @QC_REQUIRED, QC_AUTO_ADJUST = @QC_AUTO_ADJUST, SHIP_GROUP01 = @SHIP_GROUP01, SHIP_GROUP02 = @SHIP_GROUP02, SHIP_GROUP03 = @SHIP_GROUP03, TARIFF_ID = @TARIFF_ID, ORIGINAL_LINE_ID = @ORIGINAL_LINE_ID, ORIGINAL_SKU = @ORIGINAL_SKU, BUYER_PO = @BUYER_PO, ACTUAL_SHIP_DATE = @ACTUAL_SHIP_DATE, IS_SUBSTITUTE = @IS_SUBSTITUTE, OK_TO_SUBSTITUTE = @OK_TO_SUBSTITUTE, ALLOCATE_RULE_ID = @ALLOCATE_RULE_ID, PREALLOCATE_RULE_ID = @PREALLOCATE_RULE_ID, ALLOCATE_STRATEGY_TYPE = @ALLOCATE_STRATEGY_TYPE, SKU_ROTATION = @SKU_ROTATION, SHELF_LIFE = @SHELF_LIFE, ROTATION = @ROTATION, SUB_FLAG = @SUB_FLAG, WORK_ORDER_ID = @WORK_ORDER_ID, LINE_TYPE = @LINE_TYPE, COMPONENT_QTY = @COMPONENT_QTY, COMPONENT_REFERENCE = @COMPONENT_REFERENCE, LOT = @LOT, LOT_ATTR01 = @LOT_ATTR01, LOT_ATTR02 = @LOT_ATTR02, LOT_ATTR03 = @LOT_ATTR03, LOT_ATTR04 = @LOT_ATTR04, LOT_ATTR05 = @LOT_ATTR05, LOT_ATTR06 = @LOT_ATTR06, LOT_ATTR07 = @LOT_ATTR07, LOT_ATTR08 = @LOT_ATTR08, LOT_ATTR09 = @LOT_ATTR09, LOT_ATTR10 = @LOT_ATTR10, PRODUCE_DATE = @PRODUCE_DATE, EXPIRY_DATE = @EXPIRY_DATE, EXTERNAL_LOT = @EXTERNAL_LOT, EFFECTIVE_DATE = @EFFECTIVE_DATE, PALLET_ID = @PALLET_ID, ORIG_CASE_QTY = @ORIG_CASE_QTY, ORIG_PALLET_QTY = @ORIG_PALLET_QTY, OPP_REQUEST = @OPP_REQUEST, PICKING_INSTRUCTIONS = @PICKING_INSTRUCTIONS, QTY_INTRANSIT = @QTY_INTRANSIT, WP_RELEASED = @WP_RELEASED, EXTERNAL_LOC_SEQUENCE = @EXTERNAL_LOC_SEQUENCE, MIN_SHIPPER_CENT = @MIN_SHIPPER_CENT, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),VENDOR_ID = @VENDOR_ID,RECEIPT_DATE = @RECEIPT_DATE WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID  ";
        private string SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY = " UPDATE @_@ORDER_DETAIL SET ALLOCATED_QTY = ALLOCATED_QTY + @ALLOCATED_QTY, PREALLOCATED_QTY = PREALLOCATED_QTY - @ALLOCATED_QTY,   STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY_ONLY = " UPDATE @_@ORDER_DETAIL SET ALLOCATED_QTY = ALLOCATED_QTY + @ALLOCATED_QTY, OPEN_QTY = ORIGINAL_QTY - SHIPPED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_PICKED_QTY = " UPDATE @_@ORDER_DETAIL SET PICKED_QTY = PICKED_QTY + @PICKED_QTY, ALLOCATED_QTY = ALLOCATED_QTY - @PICKED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_PREALLOCATE_QTY = " UPDATE @_@ORDER_DETAIL SET PREALLOCATED_QTY = PREALLOCATED_QTY + @PREALLOCATED_QTY , OPEN_QTY = ORIGINAL_QTY - SHIPPED_QTY,  STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_REVERT_ALLOCATE_QTY = " UPDATE @_@ORDER_DETAIL SET ALLOCATED_QTY = ALLOCATED_QTY - @ALLOCATED_QTY,PREALLOCATED_QTY = PREALLOCATED_QTY + @ALLOCATED_QTY ,  STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_SHIPPED_QTY = " UPDATE @_@ORDER_DETAIL SET SHIPPED_QTY = SHIPPED_QTY + @SHIPPED_QTY, PICKED_QTY = PICKED_QTY - @SHIPPED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),ACTUAL_SHIP_DATE=@ACTUAL_SHIP_DATE WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_STATUS = " UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_STATUS_BY_ORDER_ID = " UPDATE @_@ORDER_DETAIL SET STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_UDF = " UPDATE @_@ORDER_DETAIL SET UDF1=@UDF1 , UDF2=@UDF2 , REMARK=@REMARK , UPDATED_DATE=GETDATE(), UPDATED_BY=@UPDATED_BY WHERE ORDER_ID=@ORDER_ID AND LINE_ID=@LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_UN_SHIPPED_QTY = " UPDATE @_@ORDER_DETAIL SET SHIPPED_QTY = SHIPPED_QTY - @SHIPPED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_UPDATE_ORDER_DETAIL_UN_SHIPPED_QTY_TO_PICKED = " UPDATE @_@ORDER_DETAIL SET SHIPPED_QTY = SHIPPED_QTY - @SHIPPED_QTY,PICKED_QTY=PICKED_QTY+@SHIPPED_QTY, STATUS = @STATUS ,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID ";
        private string STATUS = "@STATUS";
        private string SUB_FLAG = "@SUB_FLAG";
        private string TARIFF_ID = "@TARIFF_ID";
        private string TAX01 = "@TAX01";
        private string TAX02 = "@TAX02";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UNIT_PRICE = "@UNIT_PRICE";
        private string UOM_ID = "@UOM_ID";
        private string UPDATE_SOURCE = "@UPDATE_SOURCE";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string WH_ID = "@WH_ID";
        private string WORK_ORDER_ID = "@WORK_ORDER_ID";
        private string WP_RELEASED = "@WP_RELEASED";

        public bool CheckOrderDetailIDUnique(string orderID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_ORDER_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckOrderDetailIDUnique(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_ORDER_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteOrderDetail(List<string> orderID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_DETAIL_BY_ORDER_ID.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteOrderDetail(List<string> orderDetailID, List<string> lineIDs, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderDetailID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderDetailID[j]), new SqlParameter("LINE_ID", lineIDs[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteOrderDetail(DataBase dataBase, DbTransaction tran, List<string> orderID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_DETAIL_BY_ORDER_ID.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteOrderDetail(DataBase dataBase, DbTransaction tran, List<string> orderDetailID, List<string> lineIDs, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < orderDetailID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderDetailID[j]), new SqlParameter("LINE_ID", lineIDs[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDER_DETAIL.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteOrderDetailByOrigOrderIDAndLineID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID)
        {
            string sql = "DELETE FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND LINE_ID = @LINE_ID".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetAllOrderDetail(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllOrderDetail(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetInvQty4AllocateByStrategy(string strategy, string lot, string packQty, string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_INV_QTY_BY_ALLOCATE_STRATEGY.Replace("@_@", whLoginID);
            SqlParameter[] paras = null;
            string CS40001 = strategy;
            if (CS40001 != null)
            {
                if (!(CS40001 == "10"))
                {
                    if (CS40001 == "20")
                    {
                        sql = sql + " ORDER BY ABS(INV_LOT_LOC_LPN.QTY-INV_LOT_LOC_LPN.ALLOCATED_QTY), LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                    else if (CS40001 == "30")
                    {
                        sql = sql + " ORDER BY (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY) DESC, LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                    else if (CS40001 == "40")
                    {
                        sql = sql + " ORDER BY (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY) ASC, LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                }
                else
                {
                    sql = (sql + " AND LOCATION.LOC IN (SELECT DISTINCT LOC FROM @_@INV_SKU_LOC WHERE OWNER_ID=@OWNER_ID AND SKU_ID=@SKU_ID AND (LOCATION_TYPE='CASE' OR LOCATION_TYPE='PIECE') ) ").Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty), new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
                }
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetInvQty4AllocateByStrategy(DataBase dataBase, DbTransaction tran, string strategy, string lot, string packQty, string ownerID, string skuID, string whLoginID)
        {
            string sql = this.SQL_SELECT_INV_QTY_BY_ALLOCATE_STRATEGY.Replace("@_@", whLoginID);
            SqlParameter[] paras = null;
            string CS40001 = strategy;
            if (CS40001 != null)
            {
                if (!(CS40001 == "10"))
                {
                    if (CS40001 == "20")
                    {
                        sql = sql + " ORDER BY ABS(INV_LOT_LOC_LPN.QTY-INV_LOT_LOC_LPN.ALLOCATED_QTY), LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                    else if (CS40001 == "30")
                    {
                        sql = sql + " ORDER BY (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY) DESC, LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                    else if (CS40001 == "40")
                    {
                        sql = sql + " ORDER BY (INV_LOT_LOC_LPN.QTY - INV_LOT_LOC_LPN.ALLOCATED_QTY - INV_LOT_LOC_LPN.PICKED_QTY) ASC, LOCATION.LOGICAL_LOC ";
                        paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty) };
                    }
                }
                else
                {
                    sql = (sql + " AND LOCATION.LOC IN (SELECT DISTINCT LOC FROM @_@INV_SKU_LOC WHERE OWNER_ID=@OWNER_ID AND SKU_ID=@SKU_ID AND (LOCATION_TYPE='CASE' OR LOCATION_TYPE='PIECE') ) ").Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@LOT", lot), new SqlParameter("@PACK_QTY", packQty), new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
                }
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public int GetMaxOrderDetailLineID(string OrderID, string whLoginID)
        {
            string strSql = "SELECT MAX(LINE_ID) FROM @_@ORDER_DETAIL WHERE ORDER_ID=@ORDER_ID".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", OrderID) };
            return (int) DBHelper.ExecuteScalar(CommandType.Text, strSql, paras);
        }

        public int GetMaxOrderDetailLineID(DataBase dataBase, DbTransaction tran, string OrderID, string whLoginID)
        {
            string strSql = "SELECT MAX(LINE_ID) FROM @_@ORDER_DETAIL WHERE ORDER_ID=@ORDER_ID".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", OrderID) };
            return (int) dataBase.ExecuteScalar(tran, CommandType.Text, strSql, paras);
        }

        public OrderDetailInfo GetOrderDetailByID(string orderID, string lineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID AND OD.LINE_ID = @LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), 0M, reader["VENDOR_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), 0M, reader["STATUS_D"].ToString());
                }
            }
            return orderDetailInfo;
        }

        public OrderDetailInfo GetOrderDetailByID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID AND OD.LINE_ID = @LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrderDetailInfo orderDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00003 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), 0M, reader["VENDOR_ID"].ToString(), reader["INTRANSIT_ID"].ToString(), 0M, reader["STATUS_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return orderDetailInfo;
        }

        public DataSet GetOrderDetailByQueryList(List<string> sqlWhere, OrderDetailQueryEntity orderdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!orderdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, orderdetailQuery.CurrentPage, orderdetailQuery.PageSize, orderdetailQuery.SortField, orderdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetOrderDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, OrderDetailQueryEntity orderdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_ORDER_DETAIL_WITH_INTRANSIT.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!orderdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, orderdetailQuery.CurrentPage, orderdetailQuery.PageSize, orderdetailQuery.SortField, orderdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<OrderDetailInfo> GetOrderDetailListByIDAndOrigLineID(DataBase dataBase, DbTransaction tran, string orderID, string origLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID  AND ORIGINAL_LINE_ID = @ORIGINAL_LINE_ID  ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORIGINAL_LINE_ID", origLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString());
                orderDetailInfos.Add(orderDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderID(string orderID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID " + where + "  ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID  ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID  " + where + "  ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), 0M, reader["VENDOR_ID"].ToString(), "", 0M, reader["STATUS_D"].ToString());
                orderDetailInfos.Add(orderDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return orderDetailInfos;
        }

        public List<OrderDetailInfo> GetOrderDetailListByOrderIDAndContainerID(string orderID, string ContainerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDER_DETAILA_FOR_CONTAINER.Replace("@_@", whLoginID) + " WHERE OD.ORDER_ID = @ORDER_ID ORDER BY LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", ContainerID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrderDetailInfo> orderDetailInfos = new List<OrderDetailInfo>();
            OrderDetailInfo orderDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    orderDetailInfo = new OrderDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_DESCR"].ToString(), (reader["ORIGINAL_QTY"].ToString() != "") ? decimal.Parse(reader["ORIGINAL_QTY"].ToString()) : 0M, (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, (reader["SHIPPED_QTY"].ToString() != "") ? decimal.Parse(reader["SHIPPED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["PREALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["PREALLOCATED_QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["PICK_CODE"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LPN_ID"].ToString(), reader["STATUS"].ToString(), (reader["UNIT_PRICE"].ToString() != "") ? ((double) float.Parse(reader["UNIT_PRICE"].ToString())) : ((double) 0f), (reader["TAX01"].ToString() != "") ? ((double) float.Parse(reader["TAX01"].ToString())) : ((double) 0f), (reader["TAX02"].ToString() != "") ? ((double) float.Parse(reader["TAX02"].ToString())) : ((double) 0f), (reader["EXTENDED_PRICE"].ToString() != "") ? ((double) float.Parse(reader["EXTENDED_PRICE"].ToString())) : ((double) 0f), reader["UPDATE_SOURCE"].ToString(), (reader["PRODUCT_WEIGHT"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_WEIGHT"].ToString())) : ((double) 0f), (reader["PRODUCT_CUBE"].ToString() != "") ? ((double) float.Parse(reader["PRODUCT_CUBE"].ToString())) : ((double) 0f), reader["CARTON_BREAK"].ToString(), (reader["CARTON_QTY_BREAK"].ToString() != "") ? int.Parse(reader["CARTON_QTY_BREAK"].ToString()) : 0, reader["QC_REQUIRED"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["SHIP_GROUP01"].ToString(), reader["SHIP_GROUP02"].ToString(), reader["SHIP_GROUP03"].ToString(), reader["TARIFF_ID"].ToString(), reader["ORIGINAL_LINE_ID"].ToString(), reader["ORIGINAL_SKU"].ToString(), reader["BUYER_PO"].ToString(), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["IS_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["IS_SUBSTITUTE"].ToString()) : 0, (reader["OK_TO_SUBSTITUTE"].ToString() != "") ? int.Parse(reader["OK_TO_SUBSTITUTE"].ToString()) : 0, reader["ALLOCATE_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_STRATEGY_TYPE"].ToString(), reader["SKU_ROTATION"].ToString(), (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, reader["ROTATION"].ToString(), reader["SUB_FLAG"].ToString(), reader["WORK_ORDER_ID"].ToString(), reader["LINE_TYPE"].ToString(), (reader["COMPONENT_QTY"].ToString() != "") ? decimal.Parse(reader["COMPONENT_QTY"].ToString()) : 0M, reader["COMPONENT_REFERENCE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PALLET_ID"].ToString(), (reader["ORIG_CASE_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_CASE_QTY"].ToString()) : 0M, (reader["ORIG_PALLET_QTY"].ToString() != "") ? decimal.Parse(reader["ORIG_PALLET_QTY"].ToString()) : 0M, reader["OPP_REQUEST"].ToString(), reader["PICKING_INSTRUCTIONS"].ToString(), (reader["QTY_INTRANSIT"].ToString() != "") ? decimal.Parse(reader["QTY_INTRANSIT"].ToString()) : 0M, reader["WP_RELEASED"].ToString(), reader["EXTERNAL_LOC_SEQUENCE"].ToString(), (reader["MIN_SHIPPER_CENT"].ToString() != "") ? int.Parse(reader["MIN_SHIPPER_CENT"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), Convert.ToDecimal(reader["ENCASED_QTY"]), reader["VENDOR_ID"].ToString(), "", Convert.ToDecimal(reader["UNENCASED_QTY"]), reader["STATUS_D"].ToString());
                    orderDetailInfos.Add(orderDetailInfo);
                }
            }
            return orderDetailInfos;
        }

        public List<string> GetOrderLineIDByOrderID4Shipping(string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_GET_ORDER_LINE_ID_FOR_SHIPPING.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            List<string> orderLineIDs = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    orderLineIDs.Add(reader["LINE_ID"].ToString());
                }
            }
            return orderLineIDs;
        }

        public decimal GetPreAllocatedQtyByLineIDAndOrigLineID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string whLoginID)
        {
            string sql = "SELECT SUM(PREALLOCATED_QTY) FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID AND (LINE_ID = @LINE_ID OR ORIGINAL_LINE_ID = @LINE_ID)".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
            object obj = dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras);
            if (obj != null)
            {
                return decimal.Parse(obj.ToString());
            }
            return 0M;
        }

        public int InsertOrderDetail(OrderDetailInfo orderDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                orderDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OrderDetail_Parameters(orderDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ORDER_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertOrderDetail(DataBase dataBase, DbTransaction tran, OrderDetailInfo orderDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                orderDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OrderDetail_Parameters(orderDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ORDER_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_OrderDetail_Parameters(OrderDetailInfo orderDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.EXTERN_ORDER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.EXTERN_LINE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORIGINAL_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.OPEN_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.SHIPPED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ADJUSTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PREALLOCATED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ALLOCATED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.PICKED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.PICK_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.CARTON_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.UNIT_PRICE, SqlDbType.Float, 8), new SqlParameter(this.TAX01, SqlDbType.Float, 8), new SqlParameter(this.TAX02, SqlDbType.Float, 8), new SqlParameter(this.EXTENDED_PRICE, SqlDbType.Float, 8), new SqlParameter(this.UPDATE_SOURCE, SqlDbType.VarChar, 20), new SqlParameter(this.PRODUCT_WEIGHT, SqlDbType.Float, 8), new SqlParameter(this.PRODUCT_CUBE, SqlDbType.Float, 8), new SqlParameter(this.CARTON_BREAK, SqlDbType.VarChar, 10), new SqlParameter(this.CARTON_QTY_BREAK, SqlDbType.Int, 4), new SqlParameter(this.QC_REQUIRED, SqlDbType.VarChar, 1), new SqlParameter(this.QC_AUTO_ADJUST, SqlDbType.VarChar, 1), 
                new SqlParameter(this.SHIP_GROUP01, SqlDbType.VarChar, 1), new SqlParameter(this.SHIP_GROUP02, SqlDbType.VarChar, 1), new SqlParameter(this.SHIP_GROUP03, SqlDbType.VarChar, 1), new SqlParameter(this.TARIFF_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORIGINAL_LINE_ID, SqlDbType.VarChar, 5), new SqlParameter(this.ORIGINAL_SKU, SqlDbType.VarChar, 50), new SqlParameter(this.BUYER_PO, SqlDbType.VarChar, 20), new SqlParameter(this.ACTUAL_SHIP_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.IS_SUBSTITUTE, SqlDbType.Int, 4), new SqlParameter(this.OK_TO_SUBSTITUTE, SqlDbType.Int, 4), new SqlParameter(this.ALLOCATE_RULE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PREALLOCATE_RULE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ALLOCATE_STRATEGY_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ROTATION, SqlDbType.VarChar, 20), new SqlParameter(this.SHELF_LIFE, SqlDbType.Int, 4), new SqlParameter(this.ROTATION, SqlDbType.VarChar, 1), 
                new SqlParameter(this.SUB_FLAG, SqlDbType.VarChar, 1), new SqlParameter(this.WORK_ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_TYPE, SqlDbType.VarChar, 1), new SqlParameter(this.COMPONENT_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.COMPONENT_REFERENCE, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR02, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR03, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR04, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR05, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR06, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR07, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR08, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR09, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR10, SqlDbType.VarChar, 50), 
                new SqlParameter(this.PRODUCE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPIRY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXTERNAL_LOT, SqlDbType.VarChar, 100), new SqlParameter(this.EFFECTIVE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.PALLET_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORIG_CASE_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.ORIG_PALLET_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.OPP_REQUEST, SqlDbType.VarChar, 1), new SqlParameter(this.PICKING_INSTRUCTIONS, SqlDbType.VarChar, 0xff), new SqlParameter(this.QTY_INTRANSIT, SqlDbType.Decimal, 13), new SqlParameter(this.WP_RELEASED, SqlDbType.VarChar, 1), new SqlParameter(this.EXTERNAL_LOC_SEQUENCE, SqlDbType.VarChar, 20), new SqlParameter(this.MIN_SHIPPER_CENT, SqlDbType.Int, 4), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_DATE, SqlDbType.DateTime, 8)
             };
            if (!string.IsNullOrEmpty(orderDetailInfo.WhID))
            {
                paramArray[0].Value = orderDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = orderDetailInfo.OrderID;
            paramArray[2].Value = orderDetailInfo.LineID;
            paramArray[3].Value = orderDetailInfo.ExternOrderID;
            paramArray[4].Value = orderDetailInfo.ExternLineID;
            paramArray[5].Value = orderDetailInfo.SkuID;
            paramArray[6].Value = orderDetailInfo.AltSkuID;
            paramArray[7].Value = orderDetailInfo.OwnerID;
            paramArray[8].Value = orderDetailInfo.OriginalQty;
            paramArray[9].Value = orderDetailInfo.OpenQty;
            paramArray[10].Value = orderDetailInfo.ShippedQty;
            paramArray[11].Value = orderDetailInfo.AdjustedQty;
            paramArray[12].Value = orderDetailInfo.PreallocatedQty;
            paramArray[13].Value = orderDetailInfo.AllocatedQty;
            paramArray[14].Value = orderDetailInfo.PickedQty;
            paramArray[15].Value = orderDetailInfo.UomID;
            paramArray[0x10].Value = orderDetailInfo.PackID;
            paramArray[0x11].Value = orderDetailInfo.PickCode;
            paramArray[0x12].Value = orderDetailInfo.CartonGroup;
            paramArray[0x13].Value = orderDetailInfo.LpnID;
            paramArray[20].Value = orderDetailInfo.Status;
            paramArray[0x15].Value = orderDetailInfo.UnitPrice;
            paramArray[0x16].Value = orderDetailInfo.Tax01;
            paramArray[0x17].Value = orderDetailInfo.Tax02;
            paramArray[0x18].Value = orderDetailInfo.ExtendedPrice;
            paramArray[0x19].Value = orderDetailInfo.UpdateSource;
            paramArray[0x1a].Value = orderDetailInfo.ProductWeight;
            paramArray[0x1b].Value = orderDetailInfo.ProductCube;
            if (!string.IsNullOrEmpty(orderDetailInfo.CartonBreak))
            {
                paramArray[0x1c].Value = orderDetailInfo.CartonBreak;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            paramArray[0x1d].Value = orderDetailInfo.CartonQtyBreak;
            paramArray[30].Value = orderDetailInfo.QcRequired;
            paramArray[0x1f].Value = orderDetailInfo.QcAutoAdjust;
            paramArray[0x20].Value = orderDetailInfo.ShipGroup01;
            paramArray[0x21].Value = orderDetailInfo.ShipGroup02;
            paramArray[0x22].Value = orderDetailInfo.ShipGroup03;
            if (!string.IsNullOrEmpty(orderDetailInfo.TariffID))
            {
                paramArray[0x23].Value = orderDetailInfo.TariffID;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.OriginalLineID))
            {
                paramArray[0x24].Value = orderDetailInfo.OriginalLineID;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.OriginalSku))
            {
                paramArray[0x25].Value = orderDetailInfo.OriginalSku;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.BuyerPo))
            {
                paramArray[0x26].Value = orderDetailInfo.BuyerPo;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            DateTime CS00003 = new DateTime();
            if (orderDetailInfo.ActualShipDate != CS00003)
            {
                paramArray[0x27].Value = orderDetailInfo.ActualShipDate;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            paramArray[40].Value = orderDetailInfo.IsSubstitute;
            paramArray[0x29].Value = orderDetailInfo.OkToSubstitute;
            if (!string.IsNullOrEmpty(orderDetailInfo.AllocateRuleID))
            {
                paramArray[0x2a].Value = orderDetailInfo.AllocateRuleID;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.PreallocateRuleID))
            {
                paramArray[0x2b].Value = orderDetailInfo.PreallocateRuleID;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.AllocateStrategyType))
            {
                paramArray[0x2c].Value = orderDetailInfo.AllocateStrategyType;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.SkuRotation))
            {
                paramArray[0x2d].Value = orderDetailInfo.SkuRotation;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            paramArray[0x2e].Value = orderDetailInfo.ShelfLife;
            if (!string.IsNullOrEmpty(orderDetailInfo.Rotation))
            {
                paramArray[0x2f].Value = orderDetailInfo.Rotation;
            }
            else
            {
                paramArray[0x2f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.SubFlag))
            {
                paramArray[0x30].Value = orderDetailInfo.SubFlag;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.WorkOrderID))
            {
                paramArray[0x31].Value = orderDetailInfo.WorkOrderID;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.LineType))
            {
                paramArray[50].Value = orderDetailInfo.LineType;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            paramArray[0x33].Value = orderDetailInfo.ComponentQty;
            if (!string.IsNullOrEmpty(orderDetailInfo.ComponentReference))
            {
                paramArray[0x34].Value = orderDetailInfo.ComponentReference;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            paramArray[0x35].Value = orderDetailInfo.Lot;
            paramArray[0x36].Value = orderDetailInfo.LotAttr01;
            paramArray[0x37].Value = orderDetailInfo.LotAttr02;
            paramArray[0x38].Value = orderDetailInfo.LotAttr03;
            if (!string.IsNullOrEmpty(orderDetailInfo.LotAttr04))
            {
                paramArray[0x39].Value = orderDetailInfo.LotAttr04;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.LotAttr05))
            {
                paramArray[0x3a].Value = orderDetailInfo.LotAttr05;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            paramArray[0x3b].Value = orderDetailInfo.LotAttr06;
            paramArray[60].Value = orderDetailInfo.LotAttr07;
            paramArray[0x3d].Value = orderDetailInfo.LotAttr08;
            paramArray[0x3e].Value = orderDetailInfo.LotAttr09;
            paramArray[0x3f].Value = orderDetailInfo.LotAttr10;
            CS00003 = new DateTime();
            if (orderDetailInfo.ProduceDate != CS00003)
            {
                paramArray[0x40].Value = orderDetailInfo.ProduceDate;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (orderDetailInfo.ExpiryDate != CS00003)
            {
                paramArray[0x41].Value = orderDetailInfo.ExpiryDate;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.ExternalLot))
            {
                paramArray[0x42].Value = orderDetailInfo.ExternalLot;
            }
            else
            {
                paramArray[0x42].Value = DBNull.Value;
            }
            paramArray[0x43].Value = orderDetailInfo.EffectiveDate;
            if (!string.IsNullOrEmpty(orderDetailInfo.PalletID))
            {
                paramArray[0x44].Value = orderDetailInfo.PalletID;
            }
            else
            {
                paramArray[0x44].Value = DBNull.Value;
            }
            paramArray[0x45].Value = orderDetailInfo.OrigCaseQty;
            paramArray[70].Value = orderDetailInfo.OrigPalletQty;
            if (!string.IsNullOrEmpty(orderDetailInfo.OppRequest))
            {
                paramArray[0x47].Value = orderDetailInfo.OppRequest;
            }
            else
            {
                paramArray[0x47].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.PickingInstructions))
            {
                paramArray[0x48].Value = orderDetailInfo.PickingInstructions;
            }
            else
            {
                paramArray[0x48].Value = DBNull.Value;
            }
            paramArray[0x49].Value = orderDetailInfo.QtyIntransit;
            paramArray[0x4a].Value = orderDetailInfo.WpReleased;
            if (!string.IsNullOrEmpty(orderDetailInfo.ExternalLocSequence))
            {
                paramArray[0x4b].Value = orderDetailInfo.ExternalLocSequence;
            }
            else
            {
                paramArray[0x4b].Value = DBNull.Value;
            }
            paramArray[0x4c].Value = orderDetailInfo.MinShipperCent;
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf1))
            {
                paramArray[0x4d].Value = orderDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x4d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf2))
            {
                paramArray[0x4e].Value = orderDetailInfo.Udf2;
            }
            else
            {
                paramArray[0x4e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf3))
            {
                paramArray[0x4f].Value = orderDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x4f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf4))
            {
                paramArray[80].Value = orderDetailInfo.Udf4;
            }
            else
            {
                paramArray[80].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf5))
            {
                paramArray[0x51].Value = orderDetailInfo.Udf5;
            }
            else
            {
                paramArray[0x51].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf6))
            {
                paramArray[0x52].Value = orderDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x52].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf7))
            {
                paramArray[0x53].Value = orderDetailInfo.Udf7;
            }
            else
            {
                paramArray[0x53].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf8))
            {
                paramArray[0x54].Value = orderDetailInfo.Udf8;
            }
            else
            {
                paramArray[0x54].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf9))
            {
                paramArray[0x55].Value = orderDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x55].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Udf10))
            {
                paramArray[0x56].Value = orderDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x56].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(orderDetailInfo.Remark))
            {
                paramArray[0x57].Value = orderDetailInfo.Remark;
            }
            else
            {
                paramArray[0x57].Value = DBNull.Value;
            }
            paramArray[0x58].Value = orderDetailInfo.CreatedBy;
            paramArray[0x59].Value = orderDetailInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(orderDetailInfo.VendorID))
            {
                paramArray[90].Value = orderDetailInfo.VendorID;
            }
            else
            {
                paramArray[90].Value = DBNull.Value;
            }
            if (orderDetailInfo.ReceiptDate != new DateTime())
            {
                paramArray[0x5b].Value = orderDetailInfo.ReceiptDate;
                return paramArray;
            }
            paramArray[0x5b].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateOrderDetail(OrderDetailInfo orderDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                orderDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OrderDetail_Parameters(orderDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateOrderDetail(DataBase dataBase, DbTransaction tran, OrderDetailInfo orderDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                orderDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OrderDetail_Parameters(orderDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailActualShipDateByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string ActualShipDate, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UPDATED_DATE", ActualShipDate), new SqlParameter("@ACTUAL_SHIP_DATE", ActualShipDate), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", lineID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ACTUAL_SHIP_DATE_BY_ORDER_ID_LINE_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatus(string orderID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS_BY_ORDER_ID.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result = DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatus(DataBase dataBase, DbTransaction tran, string orderID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS_BY_ORDER_ID.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatusAndQty(string orderID, string orderLineID, string status, decimal qty, string updatedBy, string updateType, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                string CS40001 = updateType;
                if (CS40001 != null)
                {
                    if (!(CS40001 == "Status"))
                    {
                        if (CS40001 == "PreAllocatedQty")
                        {
                            goto Label_00DF;
                        }
                        if (CS40001 == "AllocatedQtyOnly")
                        {
                            goto Label_0154;
                        }
                        if (CS40001 == "AllocatedQty")
                        {
                            goto Label_01C9;
                        }
                        if (CS40001 == "PickedQty")
                        {
                            goto Label_023E;
                        }
                        if (CS40001 == "ShippedQty")
                        {
                            goto Label_02B3;
                        }
                    }
                    else
                    {
                        sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                    }
                }
                goto Label_033D;
            Label_00DF:
                sql = this.SQL_UPDATE_ORDER_DETAIL_PREALLOCATE_QTY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@PREALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                goto Label_033D;
            Label_0154:
                sql = this.SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY_ONLY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                goto Label_033D;
            Label_01C9:
                sql = this.SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                goto Label_033D;
            Label_023E:
                sql = this.SQL_UPDATE_ORDER_DETAIL_PICKED_QTY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                goto Label_033D;
            Label_02B3:
                sql = this.SQL_UPDATE_ORDER_DETAIL_SHIPPED_QTY.Replace("@_@", whLoginID);
                paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID), new SqlParameter("@ACTUAL_SHIP_DATE", DateTime.Now) };
            Label_033D:
                if (paras != null)
                {
                    result = DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailStatusAndQty(DataBase dataBase, DbTransaction tran, string orderID, string orderLineID, string status, decimal qty, string updatedBy, string updateType, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = string.Empty;
                SqlParameter[] paras = null;
                switch (updateType)
                {
                    case "Status":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_STATUS.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "PreAllocatedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_PREALLOCATE_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PREALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "AllocatedQtyOnly":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY_ONLY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "AllocatedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_ALLOCATE_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "RevertAllocatedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_REVERT_ALLOCATE_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@ALLOCATED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "PickedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_PICKED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@PICKED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID) };
                        break;

                    case "ShippedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID), new SqlParameter("@ACTUAL_SHIP_DATE", DateTime.Now) };
                        break;

                    case "UnShippedQty":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_UN_SHIPPED_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID), new SqlParameter("@ACTUAL_SHIP_DATE", DateTime.Now) };
                        break;

                    case "UnShippedQtyToPicked":
                        sql = this.SQL_UPDATE_ORDER_DETAIL_UN_SHIPPED_QTY_TO_PICKED.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@SHIPPED_QTY", qty), new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LINE_ID", orderLineID), new SqlParameter("@ACTUAL_SHIP_DATE", DateTime.Now) };
                        break;
                }
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderDetailUDF(DataBase dataBase, DbTransaction tran, OrderDetailInfo orderDetailinfo, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderDetailinfo.OrderID), new SqlParameter("@LINE_ID", orderDetailinfo.LineID), new SqlParameter("@UDF1", orderDetailinfo.Udf1), new SqlParameter("@UDF2", orderDetailinfo.Udf2), new SqlParameter("@REMARK", orderDetailinfo.Remark), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_DETAIL_UDF.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

